The Lock Diagnostics Write category provides summary information about database write lock usage. You can use this information when analyzing system performance.
Write lock usage percentages can be up to a maximum of 100% (which comprises a single lock held for the full duration of the measurement period). Highest usage activities typically comprise, for example, flushing of historic files to disk.
As a general guide, on a healthy system that is operating efficiently, you would expect write locks to be consuming less than 10% of overall database lock usage (allowing for variations for specific activities that are known to consume more resources than others). However, lock usage can be very variable and should be judged in the context of the size of the system and how that system has been configured. While there might be peaks in lock usage, you should focus on the average time that the system spends under write locks. The lower the number then the better.
For status information regarding locks in Geo SCADA Expert, see Locks. The Locks category displays all locks within the server, whereas database locks are specific to the 'database' and assist in identifying which operation is causing high lock utilization.
The database write locks are shown in rows, with each row representing a different lock. The status information shown for each lock is as follows:
- Lock Name—The Lock Name is the name of the lock in Geo SCADA Expert and indicates what or who has requested the lock. In some cases, reference is also made to:
A Server Links Id (such as L06#03), which corresponds to the Id column in the Server Links category of the Server Status Tool. This can be used to identify a client (or driver) that might be putting more load on the system than expected. For example, it might highlight that one driver is sending considerably more point updates to the database than other drivers. If this is the case, you might want to look at the Driver Point Updates category to track down the point(s) that might be causing the issue. Various driver guides include sections that explain how to adjust the configuration of points that might be causing a high load on the server (the settings that might need adjusting vary per driver).
- Lock Type—The Lock Type is either DB for the main database lock which protects all data, or Metadata which protects changes to the Metadata in Geo SCADA Expert
- Start Time—The Start Time is the time that the lock was first acquired. This is either from when Geo SCADA Expert was started, after a snapshot interval if the ResetCountersOnSnapshot log file setting is true, or after a manual reset in the Server Status tool.
- Last Access—Last Access is the last time that the lock was acquired.
- Write Locks—Write Locks is the total number of write locks, either from startup or since the lock diagnostics were last reset.
- Write Locks Per Second—Write Locks Per Second is the number of write locks per second as an average of the same period.
-
Average Write Lock—The Average Write Lock is the average time spent in a write lock for the lock.
- Longest Write Lock—Longest Write Lock is the duration of longest write lock, either since start-up or since the lock diagnostics were last reset.
- Longest Write Lock Time— This is the time that the longest write lock was released. You can subtract the Longest Write Lock duration from this time to calculate when the lock was acquired.
- Time in Write Lock—Time in Write Lock is the total time spent in write lock either since start-up or since the lock diagnostics were last reset. It is expressed as a percentage.
- Average Write Lock Wait—The Average Write Lock Wait is the average time to wait to acquire a write lock.
- Longest Write Lock Wait—Longest Write Lock Wait is the longest time to wait to acquire a write lock.
- Longest Write Lock Wait Time—The Longest Write Lock Wait Time is the timestamp of the longest time to wait to acquire a write lock.
You can access a context-sensitive menu by right-clicking on an entry in the list.
The options that are available are:
- Reset Lock Diagnostic—Select this option to reset the selected lock diagnostic.
- Reset All Lock Diagnostics—Select this option to reset all of the lock diagnostics.
- Enable Diagnostics—Select this option to enable Lock Diagnostics. The option is only available for use when the Lock Diagnostics are disabled. Locks per type are acquired.
- Disable Diagnostics—Select this option to disable all Lock Diagnostics. The comment 'Per-lock diagnostics disabled' replaces the list of locks.
These menu options apply to both database read and database write locks together. So, for example, the Reset Lock Diagnostics option will reset the write lock diagnostics and the read lock diagnostics.
Priority of Write Locks over Read Locks
With regard to lock priority, typically a database write will block further reads in order to allow the write to be performed as soon as possible after the current read. Where possible, a read lock in a query engine is released when a write is pending. Additionally, a read lock in a database save is released frequently if a write is pending. Likewise, a read lock in the standby synchronization of historic data is released if a write lock is pending. These each reduce the amount of time that a write lock has to wait for a read lock to finish.
The following diagrams indicate how database locks are typically handled.
High Priority Writes
A pending write lock will block new read locks:
A long write lock
Read Locks when a Write Lock is Detected
Where possible, read locks are released if the server detects that a write lock is pending. This reduces the amount of time that a write lock has to wait in order for a current read lock to finish:
